const express = require('express');
const router = express.Router();
const { authMiddleware } = require('../middleware/auth');
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
const Excel = require('exceljs');

// 导出美化版日报
router.get('/daily/:date/export-enhanced', async (req, res) => {
  try {
    const { date } = req.params;
    
    console.log('收到单日导出请求，日期参数:', { date });
    
    // 验证日期格式 - 使用非常宽松的验证
    console.log('尝试解析日期:', { date });
    
    // 尝试多种方式解析日期
    let startDate;
    
    try {
      // 方法1: 直接使用Date构造函数
      startDate = new Date(date);
      
      console.log('方法1解析结果:', {
        date: startDate.toString(),
        valid: !isNaN(startDate.getTime())
      });
      
      // 如果无效，尝试其他方法
      if (isNaN(startDate.getTime())) {
        throw new Error('方法1解析失败');
      }
    } catch (err) {
      console.log('尝试其他日期解析方法');
      
      // 方法2: 尝试拆分日期字符串并重新组合
      try {
        // 假设格式为YYYY-MM-DD
        const [year, month, day] = date.split('-').map(Number);
        
        startDate = new Date(year, month - 1, day);
        
        console.log('方法2解析结果:', {
          date: startDate.toString(),
          valid: !isNaN(startDate.getTime())
        });
        
        // 如果无效，尝试其他方法
        if (isNaN(startDate.getTime())) {
          throw new Error('方法2解析失败');
        }
      } catch (err2) {
        // 如果所有方法都失败，返回错误
        console.log('所有日期解析方法都失败');
        return res.status(400).json({
          status: 'error',
          message: '无法解析提供的日期格式，请使用YYYY-MM-DD格式'
        });
      }
    }
    
    // 检查日期是否有效
    if (isNaN(startDate.getTime())) {
      console.log('无效的日期');
      return res.status(400).json({
        status: 'error',
        message: '无效的日期'
      });
    }
    
    // 格式化日期为YYYY-MM-DD格式
    const formattedDate = startDate.toISOString().split('T')[0];
    
    console.log('格式化后的日期:', { formattedDate });
    
    startDate.setHours(0, 0, 0, 0);
    
    const endDate = new Date(startDate);
    endDate.setHours(23, 59, 59, 999);
    
    console.log('调整时间后的日期范围:', {
      start: startDate.toISOString(),
      end: endDate.toISOString()
    });
    
    // 确保Excel库可用
    let Excel;
    try {
      Excel = require('exceljs');
    } catch (err) {
      console.error('ExcelJS库加载失败:', err);
      return res.status(500).json({
        status: 'error',
        message: '导出功能暂时不可用，请联系管理员'
      });
    }
    
    // 查询该日期的订单
    const orders = await prisma.order.findMany({
      where: {
        createdAt: {
          gte: startDate,
          lte: endDate
        },
        status: {
          in: ['completed', 'paid']
        }
      },
      include: {
        items: {
          include: {
            product: true
          }
        },
        channel: true,
        payments: {
          select: {
            id: true,
            orderId: true,
            method: true,
            amount: true,
            status: true,
            createdAt: true,
            updatedAt: true
          }
        }
      }
    });
    
    // 计算基本统计数据
    const orderCount = orders.length;
    const totalSales = orders.reduce((sum, order) => sum + order.total, 0);
    const averageOrder = orderCount > 0 ? totalSales / orderCount : 0;
    
    // 按支付方式统计
    const paymentMethods = {};
    orders.forEach(order => {
      if (!order.payments) return;
      
      order.payments.forEach(payment => {
        const method = payment.method || 'unknown';
        paymentMethods[method] = (paymentMethods[method] || 0) + payment.amount;
      });
    });
    
    // 按渠道统计
    const channelMap = {};
    orders.forEach(order => {
      const channelId = order.channelId || 0;
      const channelName = order.channel ? order.channel.name : '未知渠道';
      
      if (!channelMap[channelId]) {
        channelMap[channelId] = {
          id: channelId,
          name: channelName,
          amount: 0,
          orderCount: 0
        };
      }
      
      channelMap[channelId].amount += order.total;
      channelMap[channelId].orderCount += 1;
    });
    const channelSales = Object.values(channelMap);
    
    // 计算热销商品
    const productMap = {};
    orders.forEach(order => {
      if (!order.items) return;
      
      order.items.forEach(item => {
        if (!item || !item.product) return;
        
        const productId = item.productId;
        const productName = item.product ? item.product.name : '未知商品';
        
        if (!productMap[productId]) {
          productMap[productId] = {
            id: productId,
            name: productName,
            quantity: 0,
            amount: 0
          };
        }
        
        productMap[productId].quantity += item.quantity;
        productMap[productId].amount += item.price * item.quantity;
      });
    });
    
    // 转换为数组并排序
    const topProducts = Object.values(productMap)
      .sort((a, b) => b.amount - a.amount);

    // 计算商品销售占比
    topProducts.forEach(product => {
      product.percentage = totalSales > 0 ? product.amount / totalSales : 0;
    });
    
    // 创建Excel工作簿和工作表
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('日报数据');
    
    // 设置页面属性
    worksheet.pageSetup.paperSize = 9; // A4
    worksheet.pageSetup.orientation = 'portrait';
    worksheet.pageSetup.fitToPage = true;
    
    // 添加标题
    worksheet.mergeCells('A1:F1');
    const titleCell = worksheet.getCell('A1');
    titleCell.value = `哈喽啤鲜生销售日报 - ${formattedDate}`;
    titleCell.font = { size: 18, bold: true, color: { argb: '000000' } };
    titleCell.alignment = { horizontal: 'center', vertical: 'middle' };
    titleCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFEBF5FF' } // 浅蓝色背景
    };
    worksheet.getRow(1).height = 30;
    
    // 添加基本统计数据
    worksheet.addRow([]);
    const headerRow = worksheet.addRow(['基本统计']);
    headerRow.font = { bold: true, size: 14 };
    headerRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F2FF' }
    };
    worksheet.mergeCells(`A${headerRow.number}:F${headerRow.number}`);
    
    // 添加基本统计表格
    const statsRow1 = worksheet.addRow(['订单数', orderCount, '', '营业额', `${totalSales.toFixed(2)}`, '元']);
    const statsRow2 = worksheet.addRow(['客单价', `${averageOrder.toFixed(2)}`, '元', '', '', '']);
    
    [statsRow1, statsRow2].forEach(row => {
      row.eachCell((cell, colNumber) => {
        if (colNumber === 1 || colNumber === 4) {
          cell.font = { bold: true };
          cell.alignment = { horizontal: 'right' };
        } else if (colNumber === 2 || colNumber === 5) {
          cell.alignment = { horizontal: 'right' };
          if (colNumber === 2 && row === statsRow1) {
            cell.font = { bold: true, color: { argb: 'FF0000FF' } }; // 蓝色
          } else if (colNumber === 5) {
            cell.font = { bold: true, color: { argb: 'FF0000FF' } }; // 蓝色
          }
        }
      });
    });
    
    worksheet.addRow([]);
    
    // 添加支付方式统计
    const paymentHeaderRow = worksheet.addRow(['支付方式统计']);
    paymentHeaderRow.font = { bold: true, size: 14 };
    paymentHeaderRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F2FF' }
    };
    worksheet.mergeCells(`A${paymentHeaderRow.number}:F${paymentHeaderRow.number}`);
    
    const paymentTableHeader = worksheet.addRow(['支付方式', '金额(元)', '', '', '', '']);
    paymentTableHeader.font = { bold: true };
    paymentTableHeader.getCell(1).alignment = { horizontal: 'center' };
    paymentTableHeader.getCell(2).alignment = { horizontal: 'center' };
    [1, 2].forEach(col => {
      paymentTableHeader.getCell(col).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFF2F2F2' }
      };
    });
    
    const paymentMethodNames = {
      'cash': '现金',
      'wechat': '支付宝',
      'alipay': '抖音外卖',
      'complimentary': '招待',
      'mixed': '混合支付',
      'unknown': '未知方式'
    };
    
    Object.entries(paymentMethods).forEach(([method, amount]) => {
      const paymentRow = worksheet.addRow([paymentMethodNames[method] || method, amount.toFixed(2), '', '', '', '']);
      paymentRow.getCell(2).alignment = { horizontal: 'right' };
      paymentRow.getCell(2).font = { color: { argb: 'FF0000FF' } }; // 蓝色
    });
    
    worksheet.addRow([]);
    
    // 添加渠道销售统计
    const channelHeaderRow = worksheet.addRow(['渠道销售统计']);
    channelHeaderRow.font = { bold: true, size: 14 };
    channelHeaderRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F2FF' }
    };
    worksheet.mergeCells(`A${channelHeaderRow.number}:F${channelHeaderRow.number}`);
    
    const channelTableHeader = worksheet.addRow(['渠道名称', '订单数', '金额(元)', '', '', '']);
    channelTableHeader.font = { bold: true };
    [1, 2, 3].forEach(col => {
      channelTableHeader.getCell(col).alignment = { horizontal: 'center' };
      channelTableHeader.getCell(col).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFF2F2F2' }
      };
    });
    
    channelSales.forEach(channel => {
      const channelRow = worksheet.addRow([channel.name, channel.orderCount, channel.amount.toFixed(2), '', '', '']);
      channelRow.getCell(1).alignment = { horizontal: 'left' };
      channelRow.getCell(2).alignment = { horizontal: 'right' };
      channelRow.getCell(3).alignment = { horizontal: 'right' };
      channelRow.getCell(2).font = { color: { argb: 'FF0000FF' } }; // 蓝色
      channelRow.getCell(3).font = { color: { argb: 'FF0000FF' } }; // 蓝色
    });
    
    worksheet.addRow([]);
    
    // 添加热销商品统计
    const productsHeaderRow = worksheet.addRow(['热销商品统计']);
    productsHeaderRow.font = { bold: true, size: 14 };
    productsHeaderRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F2FF' }
    };
    worksheet.mergeCells(`A${productsHeaderRow.number}:F${productsHeaderRow.number}`);
    
    const productsTableHeader = worksheet.addRow(['商品名称', '销售数量', '销售金额(元)', '占比(%)', '', '']);
    productsTableHeader.font = { bold: true };
    [1, 2, 3, 4].forEach(col => {
      productsTableHeader.getCell(col).alignment = { horizontal: 'center' };
      productsTableHeader.getCell(col).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFF2F2F2' }
      };
    });
    
    topProducts.forEach(product => {
      const percentage = (product.percentage * 100).toFixed(2);
      const productRow = worksheet.addRow([product.name, product.quantity, product.amount.toFixed(2), percentage, '', '']);
      productRow.getCell(1).alignment = { horizontal: 'left' };
      productRow.getCell(2).alignment = { horizontal: 'right' };
      productRow.getCell(3).alignment = { horizontal: 'right' };
      productRow.getCell(4).alignment = { horizontal: 'right' };
      productRow.getCell(2).font = { color: { argb: 'FF0000FF' } }; // 蓝色
      productRow.getCell(3).font = { color: { argb: 'FF0000FF' } }; // 蓝色
      productRow.getCell(4).font = { color: { argb: 'FF006400' } }; // 绿色
    });
    
    // 设置列宽
    worksheet.getColumn(1).width = 25;
    worksheet.getColumn(2).width = 15;
    worksheet.getColumn(3).width = 15;
    worksheet.getColumn(4).width = 15;
    worksheet.getColumn(5).width = 15;
    worksheet.getColumn(6).width = 10;
    
    // 添加边框
    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber > 1) { // 跳过标题行
        row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
          if (colNumber <= 6) {
            cell.border = {
              top: { style: 'thin' },
              left: { style: 'thin' },
              bottom: { style: 'thin' },
              right: { style: 'thin' }
            };
          }
        });
      }
    });
    
    try {
      // 设置响应头
      res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      const safeFilename = `哈喽啤鲜生销售日报-${formattedDate}.xlsx`;
      res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(safeFilename)}"`);
      res.setHeader('X-Content-Type-Options', 'nosniff');
      
      // 将工作簿写入响应
      await workbook.xlsx.write(res);
    } catch (err) {
      console.error('Excel生成失败:', err);
      return res.status(500).json({
        status: 'error',
        message: '报表生成失败',
        error: err.message
      });
    }
    
  } catch (error) {
    console.error('导出日报失败:', error);
    res.status(500).json({
      status: 'error',
      message: '导出日报失败',
      error: error.message
    });
  }
});

// 导出日期范围的美化版日报
router.get('/daily/range/export-enhanced', async (req, res) => {
  try {
    const { startDate, endDate } = req.query;
    
    console.log('收到导出请求，日期参数:', { startDate, endDate });
    console.log('请求查询参数完整内容:', req.query);
    
    if (!startDate || !endDate) {
      console.log('缺少日期参数');
      return res.status(400).json({
        status: 'error',
        message: '请提供起始日期和结束日期'
      });
    }
    
    // 验证日期格式 - 使用非常宽松的验证
    console.log('尝试解析日期:', { startDate, endDate });
    
    // 尝试多种方式解析日期
    let start, end;
    
    try {
      // 方法1: 直接使用Date构造函数
      start = new Date(startDate);
      end = new Date(endDate);
      
      console.log('方法1解析结果:', {
        start: start.toString(),
        end: end.toString(),
        startValid: !isNaN(start.getTime()),
        endValid: !isNaN(end.getTime())
      });
      
      // 如果无效，尝试其他方法
      if (isNaN(start.getTime()) || isNaN(end.getTime())) {
        throw new Error('方法1解析失败');
      }
    } catch (err) {
      console.log('尝试其他日期解析方法');
      
      // 方法2: 尝试拆分日期字符串并重新组合
      try {
        // 假设格式为YYYY-MM-DD
        const [startYear, startMonth, startDay] = startDate.split('-').map(Number);
        const [endYear, endMonth, endDay] = endDate.split('-').map(Number);
        
        start = new Date(startYear, startMonth - 1, startDay);
        end = new Date(endYear, endMonth - 1, endDay);
        
        console.log('方法2解析结果:', {
          start: start.toString(),
          end: end.toString(),
          startValid: !isNaN(start.getTime()),
          endValid: !isNaN(end.getTime())
        });
        
        // 如果无效，尝试其他方法
        if (isNaN(start.getTime()) || isNaN(end.getTime())) {
          throw new Error('方法2解析失败');
        }
      } catch (err2) {
        // 如果所有方法都失败，返回错误
        console.log('所有日期解析方法都失败');
        return res.status(400).json({
          status: 'error',
          message: '无法解析提供的日期格式，请使用YYYY-MM-DD格式'
        });
      }
    }
    
    console.log('解析后的日期对象:', { 
      start: start.toString(), 
      end: end.toString(),
      startTime: start.getTime(),
      endTime: end.getTime(),
      startIsValid: !isNaN(start.getTime()),
      endIsValid: !isNaN(end.getTime())
    });
    
    // 检查日期是否有效
    if (isNaN(start.getTime()) || isNaN(end.getTime())) {
      console.log('无效的日期');
      return res.status(400).json({
        status: 'error',
        message: '无效的日期'
      });
    }
    
    // 格式化日期为YYYY-MM-DD格式
    const formattedStartDate = start.toISOString().split('T')[0];
    const formattedEndDate = end.toISOString().split('T')[0];
    
    console.log('格式化后的日期:', { formattedStartDate, formattedEndDate });
    
    // 设置时间范围
    start.setHours(0, 0, 0, 0);
    end.setHours(23, 59, 59, 999);
    
    console.log('调整时间后的日期范围:', {
      start: start.toISOString(),
      end: end.toISOString()
    });
    
    // 确保Excel库可用
    let Excel;
    try {
      Excel = require('exceljs');
    } catch (err) {
      console.error('ExcelJS库加载失败:', err);
      return res.status(500).json({
        status: 'error',
        message: '导出功能暂时不可用，请联系管理员'
      });
    }
    
    // 查询该日期范围内的订单
    const orders = await prisma.order.findMany({
      where: {
        createdAt: {
          gte: start,
          lte: end
        },
        status: {
          in: ['completed', 'paid']
        }
      },
      include: {
        items: {
          include: {
            product: true
          }
        },
        channel: true,
        payments: true,
        user: true
      }
    });
    
    // 按日期分组
    const dailyData = {};
    orders.forEach(order => {
      const dateStr = order.createdAt.toISOString().split('T')[0];
      
      if (!dailyData[dateStr]) {
        dailyData[dateStr] = {
          date: dateStr,
          orders: [],
          totalSales: 0,
          orderCount: 0,
          paymentMethods: {},
          channelMap: {},
          productMap: {}
        };
      }
      
      dailyData[dateStr].orders.push(order);
      dailyData[dateStr].totalSales += order.total;
      dailyData[dateStr].orderCount += 1;
      
      // 按支付方式统计
      if (order.payments && order.payments.length > 0) {
        order.payments.forEach(payment => {
          const method = payment.method || 'unknown';
          if (!dailyData[dateStr].paymentMethods[method]) {
            dailyData[dateStr].paymentMethods[method] = 0;
          }
          dailyData[dateStr].paymentMethods[method] += payment.amount;
        });
      }
      
      // 按渠道统计
      const channelId = order.channelId || 0;
      const channelName = order.channel ? order.channel.name : '未知渠道';
      
      if (!dailyData[dateStr].channelMap[channelId]) {
        dailyData[dateStr].channelMap[channelId] = {
          id: channelId,
          name: channelName,
          amount: 0,
          orderCount: 0
        };
      }
      
      dailyData[dateStr].channelMap[channelId].amount += order.total;
      dailyData[dateStr].channelMap[channelId].orderCount += 1;
      
      // 计算热销商品
      if (order.items && order.items.length > 0) {
        order.items.forEach(item => {
          if (!item || !item.product) return;
          
          const productId = item.productId;
          const productName = item.product ? item.product.name : '未知商品';
          
          if (!dailyData[dateStr].productMap[productId]) {
            dailyData[dateStr].productMap[productId] = {
              id: productId,
              name: productName,
              quantity: 0,
              amount: 0
            };
          }
          
          dailyData[dateStr].productMap[productId].quantity += item.quantity;
          dailyData[dateStr].productMap[productId].amount += item.price * item.quantity;
        });
      }
    });
    
    // 计算总计数据
    const totalSales = orders.reduce((sum, order) => sum + order.total, 0);
    const orderCount = orders.length;
    const averageOrder = orderCount > 0 ? totalSales / orderCount : 0;
    
    // 创建Excel工作簿和工作表
    const workbook = new Excel.Workbook();
    
    // 创建汇总工作表
    const summarySheet = workbook.addWorksheet('汇总数据');
    
    // 设置页面属性
    summarySheet.pageSetup.paperSize = 9; // A4
    summarySheet.pageSetup.orientation = 'portrait';
    summarySheet.pageSetup.fitToPage = true;
    
    // 添加标题
    summarySheet.mergeCells('A1:F1');
    const titleCell = summarySheet.getCell('A1');
    titleCell.value = `哈喽啤鲜生销售日报汇总 - ${startDate} 至 ${endDate}`;
    titleCell.font = { size: 18, bold: true, color: { argb: '000000' } };
    titleCell.alignment = { horizontal: 'center', vertical: 'middle' };
    titleCell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFEBF5FF' } // 浅蓝色背景
    };
    summarySheet.getRow(1).height = 30;
    
    // 添加基本统计数据
    summarySheet.addRow([]);
    const headerRow = summarySheet.addRow(['基本统计']);
    headerRow.font = { bold: true, size: 14 };
    headerRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F2FF' }
    };
    summarySheet.mergeCells(`A${headerRow.number}:F${headerRow.number}`);
    
    // 添加基本统计表格
    const statsRow1 = summarySheet.addRow(['订单数', orderCount, '', '营业额', `${totalSales.toFixed(2)}`, '元']);
    const statsRow2 = summarySheet.addRow(['客单价', `${averageOrder.toFixed(2)}`, '元', '日期范围', `${startDate} 至 ${endDate}`, '']);
    
    [statsRow1, statsRow2].forEach(row => {
      row.eachCell((cell, colNumber) => {
        if (colNumber === 1 || colNumber === 4) {
          cell.font = { bold: true };
          cell.alignment = { horizontal: 'right' };
        } else if (colNumber === 2 || colNumber === 5) {
          cell.alignment = { horizontal: 'right' };
          if ((colNumber === 2 && row === statsRow1) || (colNumber === 5 && row === statsRow1)) {
            cell.font = { bold: true, color: { argb: 'FF0000FF' } }; // 蓝色
          }
        }
      });
    });
    
    summarySheet.addRow([]);
    
    // 添加日期明细表
    const dailyHeaderRow = summarySheet.addRow(['日期销售明细']);
    dailyHeaderRow.font = { bold: true, size: 14 };
    dailyHeaderRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F2FF' }
    };
    summarySheet.mergeCells(`A${dailyHeaderRow.number}:F${dailyHeaderRow.number}`);
    
    const dailyTableHeader = summarySheet.addRow(['日期', '订单数', '营业额(元)', '客单价(元)', '', '']);
    dailyTableHeader.font = { bold: true };
    [1, 2, 3, 4].forEach(col => {
      dailyTableHeader.getCell(col).alignment = { horizontal: 'center' };
      dailyTableHeader.getCell(col).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFF2F2F2' }
      };
    });
    
    // 按日期排序
    const sortedDates = Object.keys(dailyData).sort();
    
    sortedDates.forEach(dateStr => {
      const data = dailyData[dateStr];
      const avgOrder = data.orderCount > 0 ? data.totalSales / data.orderCount : 0;
      
      const dailyRow = summarySheet.addRow([
        dateStr,
        data.orderCount,
        data.totalSales.toFixed(2),
        avgOrder.toFixed(2),
        '',
        ''
      ]);
      
      dailyRow.getCell(3).alignment = { horizontal: 'right' };
      dailyRow.getCell(3).font = { color: { argb: 'FF0000FF' } }; // 蓝色
      dailyRow.getCell(4).alignment = { horizontal: 'right' };
    });
    
    // 添加合计行
    const totalRow = summarySheet.addRow([
      '合计',
      orderCount,
      totalSales.toFixed(2),
      averageOrder.toFixed(2),
      '',
      ''
    ]);
    
    totalRow.eachCell((cell, colNumber) => {
      if (colNumber <= 4) {
        cell.font = { bold: true };
        if (colNumber === 3) {
          cell.font = { bold: true, color: { argb: 'FF0000FF' } }; // 蓝色
        }
      }
    });
    
    totalRow.getCell(3).alignment = { horizontal: 'right' };
    totalRow.getCell(4).alignment = { horizontal: 'right' };
    
    summarySheet.addRow([]);
    
    // 添加支付方式统计
    const paymentHeaderRow = summarySheet.addRow(['支付方式统计']);
    paymentHeaderRow.font = { bold: true, size: 14 };
    paymentHeaderRow.getCell(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE6F2FF' }
    };
    summarySheet.mergeCells(`A${paymentHeaderRow.number}:F${paymentHeaderRow.number}`);
    
    const paymentTableHeader = summarySheet.addRow(['支付方式', '金额(元)', '占比', '', '', '']);
    paymentTableHeader.font = { bold: true };
    [1, 2, 3].forEach(col => {
      paymentTableHeader.getCell(col).alignment = { horizontal: 'center' };
      paymentTableHeader.getCell(col).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFF2F2F2' }
      };
    });
    
    // 合并所有日期的支付方式数据
    const paymentMethods = {};
    Object.values(dailyData).forEach(day => {
      Object.entries(day.paymentMethods).forEach(([method, amount]) => {
        paymentMethods[method] = (paymentMethods[method] || 0) + amount;
      });
    });
    
    const paymentMethodNames = {
      'cash': '现金',
      'wechat': '微信支付',
      'alipay': '支付宝',
      'card': '银行卡',
      'member': '会员卡',
      'coupon': '优惠券',
      'mixed': '混合支付',
      'unknown': '未知方式'
    };
    
    Object.entries(paymentMethods).forEach(([method, amount]) => {
      const percentage = totalSales > 0 ? (amount / totalSales) * 100 : 0;
      const paymentRow = summarySheet.addRow([
        paymentMethodNames[method] || method,
        amount.toFixed(2),
        `${percentage.toFixed(2)}%`,
        '',
        '',
        ''
      ]);
      paymentRow.getCell(2).alignment = { horizontal: 'right' };
      paymentRow.getCell(2).font = { color: { argb: 'FF0000FF' } }; // 蓝色
      paymentRow.getCell(3).alignment = { horizontal: 'right' };
      paymentRow.getCell(3).font = { color: { argb: 'FF008000' } }; // 绿色
    });
    
    // 设置列宽
    summarySheet.getColumn(1).width = 15;
    summarySheet.getColumn(2).width = 12;
    summarySheet.getColumn(3).width = 15;
    summarySheet.getColumn(4).width = 15;
    
    // 为每个日期创建单独的工作表
    sortedDates.forEach(dateStr => {
      const dayData = dailyData[dateStr];
      const worksheet = workbook.addWorksheet(`${dateStr}日报`);
      
      // 设置页面属性
      worksheet.pageSetup.paperSize = 9; // A4
      worksheet.pageSetup.orientation = 'portrait';
      worksheet.pageSetup.fitToPage = true;
      
      // 添加标题
      worksheet.mergeCells('A1:F1');
      const dayTitleCell = worksheet.getCell('A1');
      dayTitleCell.value = `哈喽啤鲜生销售日报 - ${dateStr}`;
      dayTitleCell.font = { size: 18, bold: true, color: { argb: '000000' } };
      dayTitleCell.alignment = { horizontal: 'center', vertical: 'middle' };
      dayTitleCell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFEBF5FF' } // 浅蓝色背景
      };
      worksheet.getRow(1).height = 30;
      
      // 添加基本统计数据
      worksheet.addRow([]);
      const dayHeaderRow = worksheet.addRow(['基本统计']);
      dayHeaderRow.font = { bold: true, size: 14 };
      dayHeaderRow.getCell(1).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFE6F2FF' }
      };
      worksheet.mergeCells(`A${dayHeaderRow.number}:F${dayHeaderRow.number}`);
      
      // 添加基本统计表格
      const dayAvgOrder = dayData.orderCount > 0 ? dayData.totalSales / dayData.orderCount : 0;
      const dayStatsRow1 = worksheet.addRow(['订单数', dayData.orderCount, '', '营业额', `${dayData.totalSales.toFixed(2)}`, '元']);
      const dayStatsRow2 = worksheet.addRow(['客单价', `${dayAvgOrder.toFixed(2)}`, '元', '', '', '']);
      
      [dayStatsRow1, dayStatsRow2].forEach(row => {
        row.eachCell((cell, colNumber) => {
          if (colNumber === 1 || colNumber === 4) {
            cell.font = { bold: true };
            cell.alignment = { horizontal: 'right' };
          } else if (colNumber === 2 || colNumber === 5) {
            cell.alignment = { horizontal: 'right' };
            if ((colNumber === 2 && row === dayStatsRow1) || (colNumber === 5 && row === dayStatsRow1)) {
              cell.font = { bold: true, color: { argb: 'FF0000FF' } }; // 蓝色
            }
          }
        });
      });
      
      worksheet.addRow([]);
      
      // 添加支付方式统计
      const dayPaymentHeaderRow = worksheet.addRow(['支付方式统计']);
      dayPaymentHeaderRow.font = { bold: true, size: 14 };
      dayPaymentHeaderRow.getCell(1).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFE6F2FF' }
      };
      worksheet.mergeCells(`A${dayPaymentHeaderRow.number}:F${dayPaymentHeaderRow.number}`);
      
      const dayPaymentTableHeader = worksheet.addRow(['支付方式', '金额(元)', '占比', '', '', '']);
      dayPaymentTableHeader.font = { bold: true };
      [1, 2, 3].forEach(col => {
        dayPaymentTableHeader.getCell(col).alignment = { horizontal: 'center' };
        dayPaymentTableHeader.getCell(col).fill = {
          type: 'pattern',
          pattern: 'solid',
          fgColor: { argb: 'FFF2F2F2' }
        };
      });
      
      Object.entries(dayData.paymentMethods).forEach(([method, amount]) => {
        const percentage = dayData.totalSales > 0 ? (amount / dayData.totalSales) * 100 : 0;
        const dayPaymentRow = worksheet.addRow([
          paymentMethodNames[method] || method,
          amount.toFixed(2),
          `${percentage.toFixed(2)}%`,
          '',
          '',
          ''
        ]);
        dayPaymentRow.getCell(2).alignment = { horizontal: 'right' };
        dayPaymentRow.getCell(2).font = { color: { argb: 'FF0000FF' } }; // 蓝色
        dayPaymentRow.getCell(3).alignment = { horizontal: 'right' };
        dayPaymentRow.getCell(3).font = { color: { argb: 'FF008000' } }; // 绿色
      });
      
      worksheet.addRow([]);
      
      // 添加渠道销售统计
      const dayChannelHeaderRow = worksheet.addRow(['渠道销售统计']);
      dayChannelHeaderRow.font = { bold: true, size: 14 };
      dayChannelHeaderRow.getCell(1).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFE6F2FF' }
      };
      worksheet.mergeCells(`A${dayChannelHeaderRow.number}:F${dayChannelHeaderRow.number}`);
      
      const dayChannelTableHeader = worksheet.addRow(['渠道名称', '订单数', '金额(元)', '占比', '', '']);
      dayChannelTableHeader.font = { bold: true };
      [1, 2, 3, 4].forEach(col => {
        dayChannelTableHeader.getCell(col).alignment = { horizontal: 'center' };
        dayChannelTableHeader.getCell(col).fill = {
          type: 'pattern',
          pattern: 'solid',
          fgColor: { argb: 'FFF2F2F2' }
        };
      });
      
      Object.values(dayData.channelMap).forEach(channel => {
        const percentage = dayData.totalSales > 0 ? (channel.amount / dayData.totalSales) * 100 : 0;
        const dayChannelRow = worksheet.addRow([
          channel.name,
          channel.orderCount,
          channel.amount.toFixed(2),
          `${percentage.toFixed(2)}%`,
          '',
          ''
        ]);
        dayChannelRow.getCell(3).alignment = { horizontal: 'right' };
        dayChannelRow.getCell(3).font = { color: { argb: 'FF0000FF' } }; // 蓝色
        dayChannelRow.getCell(4).alignment = { horizontal: 'right' };
        dayChannelRow.getCell(4).font = { color: { argb: 'FF008000' } }; // 绿色
      });
      
      worksheet.addRow([]);
      
      // 添加热销商品统计
      const dayProductHeaderRow = worksheet.addRow(['热销商品统计']);
      dayProductHeaderRow.font = { bold: true, size: 14 };
      dayProductHeaderRow.getCell(1).fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FFE6F2FF' }
      };
      worksheet.mergeCells(`A${dayProductHeaderRow.number}:F${dayProductHeaderRow.number}`);
      
      const dayProductTableHeader = worksheet.addRow(['商品名称', '销售数量', '销售金额(元)', '占比', '', '']);
      dayProductTableHeader.font = { bold: true };
      [1, 2, 3, 4].forEach(col => {
        dayProductTableHeader.getCell(col).alignment = { horizontal: 'center' };
        dayProductTableHeader.getCell(col).fill = {
          type: 'pattern',
          pattern: 'solid',
          fgColor: { argb: 'FFF2F2F2' }
        };
      });
      
      // 转换为数组并排序
      const topProducts = Object.values(dayData.productMap)
        .sort((a, b) => b.amount - a.amount)
        .slice(0, 10); // 只显示前10个热销商品
      
      topProducts.forEach(product => {
        const percentage = dayData.totalSales > 0 ? (product.amount / dayData.totalSales) * 100 : 0;
        const dayProductRow = worksheet.addRow([
          product.name,
          product.quantity,
          product.amount.toFixed(2),
          `${percentage.toFixed(2)}%`,
          '',
          ''
        ]);
        dayProductRow.getCell(3).alignment = { horizontal: 'right' };
        dayProductRow.getCell(3).font = { color: { argb: 'FF0000FF' } }; // 蓝色
        dayProductRow.getCell(4).alignment = { horizontal: 'right' };
        dayProductRow.getCell(4).font = { color: { argb: 'FF008000' } }; // 绿色
      });
      
      // 设置列宽
      worksheet.getColumn(1).width = 20;
      worksheet.getColumn(2).width = 12;
      worksheet.getColumn(3).width = 15;
      worksheet.getColumn(4).width = 10;
    });
    
    try {
      // 设置响应头
      res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      const safeFilename = `哈喽啤鲜生销售日报-${startDate}-${endDate}.xlsx`;
      res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(safeFilename)}"`);
      res.setHeader('X-Content-Type-Options', 'nosniff');
      
      // 将工作簿写入响应
      await workbook.xlsx.write(res);
    } catch (err) {
      console.error('Excel生成失败:', err);
      return res.status(500).json({
        status: 'error',
        message: '报表生成失败',
        error: err.message
      });
    }
    
  } catch (error) {
    console.error('导出日期范围日报失败:', error);
    res.status(500).json({
      status: 'error',
      message: '导出日期范围日报失败',
      error: error.message
    });
  }
});

module.exports = router; 